package morefun.utils

import java.sql.{Connection, DriverManager, Statement}

object DBUtils {

  val url = "jdbc:mysql://114.55.2.82:3306/ddtv_test"
  val username = "ddtvtest"
  val password = "ddtv_wx"

  classOf[com.mysql.jdbc.Driver]

  def getConnection(): Connection = {
    val conn = DriverManager.getConnection(url, username, password)

    // 暂不设置 autoCommit，如设置只有当  conn.commit 才会真正执行 sql
    //conn.setAutoCommit(false);
    conn
  }

  def release(conn: Connection): Unit = {
    release(conn, null)
  }

  def release(conn: Connection, stateMent: Statement): Unit = {
      try{
        if(!stateMent.isClosed() || stateMent != null){
          stateMent.close()
        }
        if(!conn.isClosed() || conn != null){
          conn.close()
        }
      }
      catch {
        case ex: Exception => {
          ex.printStackTrace()
        }
      }
  }

  /**
    * 演示如何利用 createStatement + addBatch 批量执行 SQL 语句
    * 适用于批量执行不同的 SQL 语句
    */
  def test1(): Unit = {

    // 1. 获取 connection
    val conn = DBUtils.getConnection()
    // 2. 设置 SQL Statement
    val pstmInsert = conn.createStatement();//对象中有一个集合对象list

    pstmInsert.clearBatch()

    try{

      // 设置 SQL 语句，这里可以放入不同的 SQL 语句
      for(i <- 1 to 1000) {
        val sqlTest = "INSERT INTO message_log_bak(chat_id,chat_room_name,user_id, content, create_time, type)" +
          " VALUES(" + i + ", " + "'test1'" + ",'33','66', '2017-08-20 00:00:00', '4')"
        pstmInsert.addBatch(sqlTest);
//        println(" addBatch index = " + i)

        // 防止出现 outofmemoryError
        if ((i % 100) == 0) {
          val rs = pstmInsert.executeBatch
          println(" 分段执行，影响行数：" + rs.size)
          pstmInsert.clearBatch
        }
      }

      // 3. 执行 SQL
      val rs = pstmInsert.executeBatch()
      println(" 影响行数：" + rs.size)

      // 4. clear batch
      pstmInsert.clearBatch()
    }
    finally {
      // 5. 释放资源
      DBUtils.release(conn, pstmInsert)
    }
  }


  /**
    * 演示如何利用 prepareStatement + addBatch 执行可设置多参数的 SQL 语句
    * 适用于执行一个多参数的 SQL 语句，如 inser
    */
  def test2(): Unit = {

    // 1. 获取 connection
    val conn = DBUtils.getConnection()
    // 2. 设置 SQL
    val sqlInsert = new StringBuilder()
      .append("INSERT INTO message_log_bak(chat_id,chat_room_name,user_id, content, create_time, type)")
      .append("     VALUES(?, ?, ?, ?, ?, ?)")


    val pstmInsert = conn.prepareStatement(sqlInsert.toString())

    pstmInsert.clearBatch()

    try{
      for(i <- 1 to 100) {
        pstmInsert.setObject(1, i)
        pstmInsert.setObject(2, "test2")
        pstmInsert.setObject(3, "44")
        pstmInsert.setObject(4, "33")
        pstmInsert.setObject(5, "2017-08-20 00:00:00")
        pstmInsert.setObject(6, "3")
        pstmInsert.addBatch()
//        println(" addBatch index = " + i)

        // 防止出现 outofmemoryError
        if ((i % 10) == 0) {
          val rs = pstmInsert.executeBatch
          println(" 分段执行，影响行数：" + rs.size)
          pstmInsert.clearBatch
        }
      }

      // 3. 执行 SQL
      val rs = pstmInsert.executeBatch()
      println(" 影响行数：" + rs.size)

      // 4. clear batch
      pstmInsert.clearBatch()
    }
    finally {
//      conn.commit()
      // 5. 释放资源
      pstmInsert.clearBatch()
      DBUtils.release(conn, pstmInsert)
    }
  }

  /**
    * 演示如何利用 executeUpdate 来执行一条 SQL
    * 相比较 executeBatch，executeUpdate 更注重执行及时性
    */
  def test3(): Unit = {
    // 1. 获取 connection
    val conn = DBUtils.getConnection()
    // 2. 设置 SQL
    val sqlInsert = new StringBuilder()
      .append("INSERT INTO message_log_bak(chat_id,chat_room_name,user_id, content, create_time, type)")
      .append("     VALUES(?, ?, ?, ?, ?, ?)")

    val pstmInsert = conn.prepareStatement(sqlInsert.toString())

    pstmInsert.clearBatch()

    try{
        pstmInsert.setObject(1, 1)
        pstmInsert.setObject(2, "test2")
        pstmInsert.setObject(3, "44")
        pstmInsert.setObject(4, "33")
        pstmInsert.setObject(5, "2017-08-20 00:00:00")
        pstmInsert.setObject(6, "3")
        pstmInsert.addBatch()
//        println(" addBatch index = " + i)


      // 3. 执行 SQL
      val rs = pstmInsert.executeUpdate()
      println(" 影响行数：" + rs)

    }
    finally {
      // 4. 释放资源
      DBUtils.release(conn, pstmInsert)
    }
  }

  /**
    * 演示如何使用 executeQuery 来执行查询语句
    */
  def test4(): Unit = {


    // 1. 获取 connection
    val conn = DBUtils.getConnection()
    // 2. 创建 statement
    val st = conn.createStatement()

    try{

      // 3. 执行 SQL 语句
      val rs = st.executeQuery("SELECT * FROM message_log_bak LIMIT 10")

      // 4. 打印返回值
      while (rs.next) {
        println(rs.getString("id"))
        println(rs.getString("user_id"))
        println(rs.getString("chat_room_name"))
      }

    }
    finally {
      // 5. 释放资源
      DBUtils.release(conn, st)
    }
  }


  def main(args: Array[String]): Unit = {
//    test1();
    test2();
//    test3();
//    test4();
  }
}
